<?php
class M_enrollees extends CI_Model {
	
	private $__table = 'enrollments';
	
	public function get_all_enrollees($sy_from=FALSE,$sy_to=FALSE)
	{
		
		$query = 'SELECT
					sections.section_name,
					sections.section_status,
					enrollments.e_profile_id,
					enrollments.e_section_id,
					enrollments.e_enrollee_type,
					levels.level_code,
					levels.level_desc,
					enrollments.e_grade_level,
					enrollments.e_id,
					enrollments.e_sy_from,
					enrollments.e_sy_to,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename,
					FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					LEFT JOIN sections ON enrollments.e_section_id = sections.id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
					WHERE enrollments.e_sy_from = \''.$sy_from.'\' AND enrollments.e_sy_to = \''.$sy_to.'\'
					';
					
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
		
		//$query = $this->db->where('e_sy_from',$sy_from)->where('e_sy_to',$sy_to)->get($this->__table);
		
		//return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	/*
		query to count all enrollees for pagination
	*/	
	public function count_enrollees($sy_from=FALSE,$sy_to=FALSE)
	{
		return $this->db->where('e_sy_from',$sy_from)->where('e_sy_to',$sy_to)->count_all($this->__table);
	}
	
	/*
		query to fetch all enrollees for pagination
	*/	
	public function fetch_all_enrollees($sy_from,$sy_to,$limit, $start)
	{
        //$this->db->limit($limit, $start);
		
		$q = "SELECT
					enrollments.e_id,
					enrollments.e_created_at,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename,
					profiles.profile_id
					FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					WHERE enrollments.e_sy_from = ".$sy_from." 
					AND enrollments.e_sy_to = ".$sy_to."
					AND enrollments.e_registered = 0
					LIMIT ".$start.",".$limit."
					";
		
        //$query = $this->db->where('e_sy_from',$sy_from)->where('e_sy_to',$sy_to)->get($this->__table);
		$query = $this->db->query($q);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	function search_enrollee($lastname, $firstname, $idno, $sy_from, $sy_to){
		$query = "SELECT
					sections.section_name,
					sections.section_status,
					enrollments.e_profile_id,
					enrollments.e_section_id,
					enrollments.e_enrollee_type,
					levels.level_code,
					levels.level_desc,
					enrollments.e_grade_level,
					enrollments.e_id,
					enrollments.e_sy_from,
					enrollments.e_sy_to,
					enrollments.e_profile_id,
					profiles.user_id,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename
				FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					LEFT JOIN sections ON enrollments.e_section_id = sections.id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
				WHERE 
					profiles.firstname LIKE '%$firstname%' 
						AND 
					profiles.lastname LIKE '%$lastname%' 
						AND 
					profiles.idno LIKE '%$idno%' 
						AND 
					(enrollments.e_sy_from = '$sy_from' AND enrollments.e_sy_to = '$sy_to')
						AND enrollments.e_registered = 0
					";
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	function search_student($lastname, $firstname, $idno, $sy_from, $sy_to){
		$query = "SELECT
					sections.section_name,
					sections.section_status,
					enrollments.e_profile_id,
					enrollments.e_section_id,
					enrollments.e_enrollee_type,
					levels.level_code,
					levels.level_desc,
					enrollments.e_grade_level,
					enrollments.e_id,
					enrollments.e_sy_from,
					enrollments.e_sy_to,
					enrollments.e_profile_id,
					profiles.user_id,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename
				FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					LEFT JOIN sections ON enrollments.e_section_id = sections.id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
				WHERE 
					profiles.firstname LIKE '%$firstname%' 
						AND 
					profiles.lastname LIKE '%$lastname%' 
						AND 
					profiles.idno LIKE '%$idno%' 
						AND 
					(enrollments.e_sy_from = '$sy_from' AND enrollments.e_sy_to = '$sy_to')
						AND enrollments.e_registered = 1
					";
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_enrollee_profile($id)
	{
		$sql = 'SELECT 
					   profile_id,
					   firstname as `First Name`,
					   lastname as `Last Name`,
					   middlename as `Middle Name`,
					   student_type as `Student Type`,
					   gender as `Gender`,
					   civilstatus as `Civil Status`,
					   dob as `Date of Birth`,
					   pob as `Place of Birth`,
					   age as `Age`,
					   disability as `Disability`,
					   nationality as `Nationality`,
					   religion as `Religion`,
					   contact_no as `Contact Number`,
					   email as `Email Address`,
					   present_address as `Present Address`,
					   fathername as `Father\'s Name`,
					   father_occupation as `Father\s Occupation`,
					   father_contact_no as `father\'s Contact #`,
					   mothername as `Mother\'s Name`,
					   mother_occupation as `Mother\'s Occupation`,
					   mother_contact_no as `mother\'s Contact #`,
					   parent_address as `Parent\'s Address`,
					   guardian_name as `Guardian\'s Name`,
					   relationship as `Guardian\'s Relationship`,
					   guardian_contact_no as `Guardian\'s Contac #`,
					   guardian_address as `Guardian\'s Address`,
					   last_school_name as `last School Attended`,
					   last_school_address as `Address`,
					   last_school_level as `level`,
					   last_school_year as `Year`
			FROM profiles where profile_id = ?';
		$get_profile_query = $this->db->query($sql,array($id));
		return $get_profile_query->num_rows() > 0 ? $get_profile_query->result_array() : FALSE;
	}
	
	
	
	public function get_enrollee_profile_all($profile_id){
		$query = "SELECT 
					   profiles.profile_id,
					   enrollee_id,
					   idno,
					   firstname,
					   lastname,
					   middlename,
					   student_type,
					   gender,
					   civilstatus,
					   dob,
					   pob,
					   age,
					   disability,
					   nationality,
					   religion,
					   contact_no,
					   email,
					   present_address,
					   fathername,
					   father_occupation,
					   father_contact_no,
					   mothername,
					   mother_occupation,
					   mother_contact_no,
					   parent_address,
					   guardian_name,
					   relationship,
					   guardian_contact_no,
					   guardian_address,
					   last_school_name,
					   last_school_address,
					   last_school_level,
					   last_school_year,
					   sy_from,
                       sy_to,
                       section_id,
                       other_info.level_id,
                       level_code,
                       level_desc,
                       section_name
				FROM profiles 
                LEFT JOIN (
					SELECT 
						enrollments.e_id AS enrollee_id,
						enrollments.e_section_id AS section_id, 
				        enrollments.e_sy_from AS sy_from,
				        enrollments.e_sy_to AS sy_to,
						e_profile_id AS profile_id, 
				        e_grade_level AS level_id, 
				        levels.level_code,
				        levels.level_desc,
				        sections.section_name
        			FROM enrollments 
					LEFT JOIN levels ON levels.level_id = enrollments.e_grade_level
					LEFT JOIN sections ON sections.id = enrollments.e_id
					WHERE enrollments.e_profile_id = '$profile_id'
					) AS other_info 
				ON 
					profiles.profile_id = other_info.profile_id
                WHERE profiles.profile_id = '$profile_id'";
				
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	
	public function get_enrollee_fees($e_id,$gp,$sy)
	{
		$sql = "SELECT  sef.sef_fee_name,
						sef.sef_fee_rate ,
						sef.sef_id
				FROM student_enrollment_fees sef
				where sef.sef_enrollment_id = ?
				AND sef.sef_gperiod_id = ?
				AND sef.sef_schoolyear_id = ?;";
		$query = $this->db->query($sql,array($e_id,$gp,$sy));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_available_fees($e_id)
	{
		$all_fee_query = $this->db->select(array('fee_rate as sef_fee_rate','fee_name as sef_fee_name'))->get('fees');
		if($all_fee_query->num_rows() > 0)
		{
			$sel_fee_query = $this->db->select(array('sef_fee_name','sef_fee_rate'))->where('sef_enrollment_id',$e_id)->get('student_enrollment_fees');
			if($sel_fee_query->num_rows() > 0)
			{
				$all_fees = $all_fee_query->result_array();
				$sel_fees = $sel_fee_query->result_array();
				
				
				$names = array_map(function($value){
				return $value['sef_fee_name'];
				},$sel_fees);
				
				$new = array_filter($all_fees, function($value) use ($names){
				return !in_array($value['sef_fee_name'], $names);
				});
				
				return $new;
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
	}
	
	function assign_enrollee_to_section($e_profile_id, $section_id){
		$this->db->where('e_profile_id', $e_profile_id)->update('enrollments', array('e_section_id' => $section_id));
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	function get_enrollee_section_id($e_profile_id){
		$q = $this->db->select('e_section_id')->where('e_profile_id', $e_profile_id)->get($this->__table);
		return $q->num_rows() >= 1 ? $q->row()->e_section_id : FALSE;
	}
	
	function update_enrollee_profile_all($profile_data, $enrollment_data, $profile_id){
		$this->db->where('e_profile_id', $profile_id)->update('enrollments', $enrollment_data);
		if($this->db->affected_rows() >= 1){
			$this->db->where('profile_id', $profile_id)->update('profiles', $profile_data);
			return $this->db->affected_rows() >= 1 ? 'success' : 'profile_failed';
		}
		else
			return FALSE;
	}
	
	function destroy_enrollee($enrollment_id){
		$this->db->where('e_id', $enrollment_id)->delete('enrollments');
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	function destroy_account($user_id, $profile_id){
		$this->db->where('profile_id', $profile_id)->delete('enrolled_subjects');
		$this->db->where('e_profile_id', $profile_id)->delete('enrollments');
		$this->db->where('id', $user_id)->delete('users');
		
		return true;
	}
	
	public function cashier_add_new_fees($data)
	{
			$this->db->insert_batch('student_enrollment_fees',$data);
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function get_sum_of_fees($id,$gp,$sy)
	{
		$sql = 'SELECT sum(sef_fee_rate) as sef_fee_rate
			   FROM student_enrollment_fees
			   WHERE sef_gperiod_id = ? 
			   AND sef_schoolyear_id = ? 
			   AND sef_enrollment_id = ?';				  
		$query = $this->db->query($sql,array($gp,$sy,$id));
		if($query->num_rows() > 0)
		{
			$row = $query->row();
			return $row->sef_fee_rate;
		}else
		{
			return 'No Data';
		}
	}
	
	public function get_sum_of_paid_fees($id,$gperiod,$sy)
	{
	
		$sql = 'SELECT sum(spr_ammt_paid) as spr_ammt_paid
			   FROM student_payment_records
			   WHERE spr_gperiod_id = ?
			   AND spr_schoolyear_id = ?
			   AND spr_enrollment_id = ?';
	
		$query = $this->db->query($sql,array($gperiod,$sy,$id));
		if($query->num_rows() > 0)
		{
			$row = $query->row();
			return $row->spr_ammt_paid;
		}else
		{
			return 'No Data';
		}
	}
	
	public function delete_stud_fee($sef_id,$e_id)
	{
		$this->db->where('sef_id',$sef_id)->where('sef_enrollment_id',$e_id)->delete('student_enrollment_fees');
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function get_enrollee_prof($e_id,$p_id)
	{
		$sql= "SELECT 	p.idno,
						p.profile_id,
						e.e_id,
						l.level_desc,
						l.level_id,
						concat(p.lastname,' ',p.firstname,' ',p.middlename)as fullname
				FROM profiles p,levels l,enrollments e
				WHERE p.profile_id = e.e_profile_id
				AND e.e_grade_level = p.level_id
				AND e.e_grade_level = l.level_id
				AND p.profile_id = ?
				AND e.e_id = ?";
		$query = $this->db->query($sql,array($p_id,$e_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function check_level_fee_status($l_id)
	{
		$sql = "SELECT 	lf_fee_id,
						fee_name,
						fee_rate 
				FROM level_fees lf,fees f 
				WHERE lf.lf_fee_id = f.fee_id 
				AND lf_level_id = ?";
		$query = $this->db->query($sql,array($l_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_scholarships()
	{
		$query = $this->db->get('student_scholarships');
		return $query->num_rows() > 0 ? $query->result() : FALSE;
		
	}
}
?>